﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;


namespace ClassAdd
{
    class DBmanager
    {
        //public String listOfTables;
        public List<String> listOfTables = new List<String>();
        public List<String> listOfVariables = new List<String>();
            SqlConnection conn = new SqlConnection(@"Data Source=PC-BRS1-4-50;Initial Catalog=EurotaxAudytEPOS01;Persist Security Info=True;User ID=sa;Password=User123");
            
        
        public void connectToDB()
        {
            conn.Open();
            String strSQL = "Select * FROM dbo.sysobjects WHERE xtype = 'U'";
            SqlCommand sqlCommand = new SqlCommand(strSQL, conn);
            SqlDataReader dataReader = sqlCommand.ExecuteReader();
            
            while (dataReader.Read())
            {
                //listOfTables = listOfTables + '\n' + dataReader["name"].ToString().Trim();
                listOfTables.Add(dataReader["name"].ToString().Trim());
                listOfTables.Sort();
                
            }

            conn.Close();
        }
        public void getVaiables (String tableName)
        {
            String temp = "";
            conn.Open();
            String strSql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + tableName + "'";
            SqlCommand sqlCommand = new SqlCommand(strSql,conn);
            SqlDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                if (dataReader[7].ToString().Contains("nvarchar") == true)
                    temp = "string";
                else if (dataReader[7].ToString().Contains("tinyint") == true)
                    temp = "int";
                else if (dataReader[7].ToString().Contains("smalldatetime") == true)
                    temp = "string";
                else
                    temp = dataReader[7].ToString().Trim();


                
                listOfVariables.Add(temp + " " + dataReader[3].ToString().Trim());
            }
            
        }
    }
}
